package com.hnkywl.sts.dao.sys;

import com.hnkywl.sts.entity.sys.Pm;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 说明：
 * 包:        com.hnkywl.sts.dao.sys
 * 类描述:    品名dao类
 * 创建人:    ccr
 * 创建时间: 13-2-18 上午16:09
 * 修改人:    ccr
 * 修改时间: 13-2-18 上午16:09
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class PmDao extends SimpleHibernateTemplate<Pm,Long>{
    /**
     * 方法名:     get
     * 功能描述:   根据属性查找所有品名并分页显示
     * 输入参数:   @param name 品名名称
     *                   code 品名代码
     *                   remark 品名描述
     *                   rjwzbmId 二级编码物资Id
     *                   pageNo 分页组件参数，当前页
     *                   pageSize 分页组件参数 页面展示的数量
     *                   total 分页组件参数 总数量
     * 返回值:     @return Page<Pm> 品名的分页组件
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-19 下午15:57
     * 修改人:     ccr
     * 修改日期:   13-2-19 下午15:57
     */
    public Page<Pm> get(String type,String name, String code, Long rjwzbmId, int pageNo,  int pageSize) {
        String bmylz = type!=null&&type.equals("bg") ? "bmylz2" : "bmylz3";//"jy"经营 "bg"保供
        Page<Pm> page = new Page<Pm>(pageNo,pageSize,0);
        String select = "select wzbm.id as \"id\",wzbm.name as \"name\",bm.@bmylz as \"code\",wzbm.remark as \"remark\",rjbm.name as \"rjwzbm.name\",3 as \"level\" ";
        String from = "from t_sys_yjwzbm wzbm left join t_sys_rjwzbm rjbm on(wzbm.rjwzbm_id = rjbm.id) left join t_sys_bmb bm on(wzbm.code = bm.bmz)";
        StringBuilder where = new StringBuilder();
        where.append("bm.zt = 1 and bm.@bmylz is not null");
        if(StringUtils.isNotEmpty(name)){
            if(where.length()!=0)
                where.append(" and ");
            where.append("wzbm.name like :name");
        }
        if(StringUtils.isNotEmpty(code)){
            if(where.length()!=0)
                where.append(" and ");
            where.append("bm.@bmylz = :name");
        }
        if(rjwzbmId != null) {
            if(where.length()!=0)
                where.append(" and ");
            where.append("rjbm.id = :rjwzbmId");
        }
        String orderBy = " order by bm.@bmylz asc";
        String count = "select count(1) " + from + (where.length()!=0?" where ":"") + where;
        String sql = select + from + (where.length()!=0?" where ":"") + where + orderBy;
        count = count.replaceAll("@bmylz",bmylz);
        sql = sql.replaceAll("@bmylz", bmylz);
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        SQLQuery countQuery = getSession().createSQLQuery(count);
        if(StringUtils.isNotEmpty(name)){
            countQuery.setString("name","'%"+name.trim()+"%'");
            sqlQuery.setString("name","'%"+name.trim()+"%'");
        }
        if(StringUtils.isNotEmpty(code)){
            countQuery.setString("code",code);
            sqlQuery.setString("code", code);
        }
        if(rjwzbmId!=null){
            countQuery.setLong("rjwzbmId",rjwzbmId);
            sqlQuery.setLong("rjwzbmId", rjwzbmId);
        }
        page.setTotalCount(((BigDecimal)countQuery.uniqueResult()).intValue());
        if(page.isFirstSetted())
            sqlQuery.setFirstResult(page.getFirst());
        if(page.isPageSizeSetted())
            sqlQuery.setMaxResults(page.getPageSize());
        sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(sqlQuery.list());
        return page;
    }

    public Page<Pm> findGridJson(Long gsId,Long sjwzbmId,int pageNo,int pageSize){
        Page<Pm> page = new Page<Pm>(pageNo,pageSize,0);
        String select = "select wzbm.id as \"id\",wzbm.name as \"name\",bm.bmz as \"code\",sjbm.name as \"rjwzbm_name\"";
        String from = " from t_sys_bmfw bmfw left join t_sys_pm wzbm on (bmfw.wzbmid = wzbm.id) " +
                "left join t_sys_bmb bm on(wzbm.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id) " +
                "left join t_sys_rjwzbm sjbm on(wzbm.rjwzbm_id = sjbm.id)";
        StringBuilder where = new StringBuilder();
        where.append("bm.bmz is not null and lower(bmlx.lxzjm) = 'pm' and bm.zt = '1'");
        if(gsId != null) {
            if(where.length()!=0)
                where.append(" and ");
            where.append("bmfw.gsId = :gsId and bmfw.type = 3");
        } else {
            from = " from t_sys_pm wzbm left join t_sys_bmb bm on(wzbm.code = bm.bmz) left join t_sys_bmlx bmlx on(bm.bmlxid = bmlx.id) "+
                    "left join t_sys_rjwzbm sjbm on(wzbm.rjwzbm_id = sjbm.id)";
        }
        if(sjwzbmId != null) {
            if(where.length()!=0)
                where.append(" and ");
            where.append("sjbm.id = :sjbmId");
        }
        String queryString = select+from+(where.length()!=0?" where ":"")+where;
        String countString = "select count(1) "+from+(where.length()!=0?" where ":"")+where;
        SQLQuery query = getSession().createSQLQuery(queryString);
        SQLQuery count = getSession().createSQLQuery(countString);
        if(gsId != null) {
            query.setLong("gsId",gsId);
            count.setLong("gsId",gsId);
        }
        if(sjwzbmId!=null && sjwzbmId!=0){
            query.setLong("sjbmId",sjwzbmId);
            count.setLong("sjbmId",sjwzbmId);
        }
        if(page.isFirstSetted())
            query.setFirstResult(page.getFirst());
        if(page.isPageSizeSetted())
            query.setMaxResults(page.getPageSize());
        page.setTotalCount(((BigDecimal)count.uniqueResult()).intValue());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        page.setResult(query.list());
        return page;
    }

    /**
     * 方法名:     getWzbmTreeJson
     * 功能描述:   返回品名树的Json字符串
     * 输入参数:   @param rjwzbmId 一级编码物资的Id
     * 返回值:     @return String {id:id,name:name,level:level,children:[]}
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-19 下午17:19
     * 修改人:     ccr
     * 修改日期:   13-2-19 下午17:19
     */
    public String getWzbmTreeJson(Long rjwzbmId) {
        try {
            String nodes = "[";
            StringBuffer json = new StringBuffer();
            String hql = "from Pm where rjwzbm.id = ?";
            List<Pm> result = find(hql,rjwzbmId);
            if (result != null && !result.isEmpty()) {
                for (int i = 0; i < result.size(); i++) {
                    Pm o = result.get(i);
                    json.append("{\"id\":\"");
                    json.append(o.getId()).append(":").append("3");
                    json.append("\",\"text\":\"");
                    json.append(o.getName());
                    json.append("\",\"attributes\":{\"level\":\"3\"");
                    json.append(",\"children\":\"true\"},");
                    json.append("\"state\":\"open\"");
                    json.append("},");
                }
                nodes += json.substring(0, json.length() - 1);
            }
            nodes += "]";
            return nodes;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "[]";
    }
    /**
     * 方法名:     checkName
     * 功能描述:   判断品名名称是否重复
     * 输入参数:   @param id 品名的Id若为空则为新增校验，否则修改校验
     *                   name 品名的名称
     * 返回值:     @return boolean true表示数据库中已存在，false表示数据库中不存在
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-20 下午12:57
     * 修改人:     ccr
     * 修改日期:   13-2-20 下午12:57
     */
    public boolean checkName(Long id,String name) {
        if (id == null){
            //新增校验
            String hql = "from Pm where name = ?";
            return existsQuery(hql,name);
        } else {
            //修改校验
            String hql = "from Pm where id<>? and name = ?";
            return existsQuery(hql,id,name);
        }
    }

    /**
     * 方法名:     checkName
     * 功能描述:   判断品名代码是否重复
     * 输入参数:   @param id 品名的Id若为空则为新增校验，否则修改校验
     *                   name 品名的代码
     * 返回值:     @return boolean true表示数据库中已存在，false表示数据库中不存在
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-20 下午12:57
     * 修改人:     ccr
     * 修改日期:   13-2-20 下午12:57
     */
    public boolean checkCode(Long id,String code) {
        if (id == null){
            //新增校验
            String hql = "from Pm where code = ?";
            return existsQuery(hql,code);
        } else {
            //修改校验
            String hql = "from Pm where id<>? and code = ?";
            return existsQuery(hql,id,code);
        }
    }

    public Pm findByCode(String code) {
        String hql = "select wzbm from Pm wzbm,Bm bm where wzbm.code = bm.bmz and bm.zt = 1 and wzbm.code = ?";
        return (Pm) findUnique(hql,code);
    }

    public boolean existGg(String code){
        String hql = "select wzbm from Gg wzbm,Bm bm where wzbm.code = bm.bmz and bm.zt = 1 and bm.bmylz2 is not null and wzbm.pm.code = ?";
        return existsQuery(hql,code);
    }
    public boolean existGgJy(Long id){
        String hql = "select wzbm from Gg wzbm,Bm bm where wzbm.code = bm.bmz and bm.zt = 1 and wzbm.pm.id = ?";
        return existsQuery(hql,id);
    }

    public List<Map> findAllPm(Long gsid){
        String hql = "select code,name from t_sys_pm where id in (select distinct pm_id from v_wzbm_gg_search where gsid=?)";
        return  (List<Map>)createSQLQuery(hql,gsid).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }
}
